package com.edu.common.utils.poi;

import cn.hutool.core.util.RandomUtil;
import com.edu.common.config.ProjectConfig;
import com.edu.common.constant.Constants;
import com.edu.common.utils.file.FileTypeUtils;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.stream.Stream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @program: xxxx_2
 * @description:
 * @author: zlh
 * @create: 2024-01-10 10:45
 **/
public class ExcelReaderUtil {

  private static final Logger log = LoggerFactory.getLogger(ExcelReaderUtil.class);

  /**
   * 读取远程excel文件
   * @param filePath
   * @return
   */
  public static Map<String, List<List<String>>> readExcelFromUrl(String filePath){
    Map<String, List<List<String>>> lists = new HashMap<>();
    try{
      String localPath = ProjectConfig.getProfile();
      String replace = filePath.replace(Constants.RESOURCE_PREFIX, "");
      File file = new File(localPath + replace);
      lists = readExcelMultiSheet(file);
//      file.delete();
    } catch (Exception e){
      log.error("读取excel异常3", e);
    }

    return lists;
  }

  public static Map<String, List<List<String>>> readExcelMultiSheet(File file){
    FileInputStream fileInputStream = null;
    Workbook workbook = null;
    Map<String, List<List<String>>> resMap = new HashMap<>();

    try {
      fileInputStream = new FileInputStream(file);
      if (FileTypeUtils.getFileType(file).equals("xls")){
        workbook = new HSSFWorkbook(fileInputStream);
      } else {
        workbook = new XSSFWorkbook(fileInputStream);
      }

      int numberOfSheets = workbook.getNumberOfSheets();

      for (int i=0 ;i<numberOfSheets; i++){
        // begin
        List<List<String>> resList = new ArrayList<>();
        Sheet sheet = workbook.getSheetAt(i); // 获取第一个工作表

        Short aShort = Optional.ofNullable(sheet.getRow(0)).map(Row::getLastCellNum)
            .orElseGet(() -> (short) 7);
        String[][] resArr = new String[sheet.getLastRowNum()+1][aShort];
        for (Row row : sheet) {
//        List<String> res = new ArrayList<>();
          String item = "";
          for (Cell cell : row) {
            CellType cellType = cell.getCellType();
            switch (cellType) {
              case STRING:
                item = cell.getStringCellValue();
                break;
              case NUMERIC:
                item = String.valueOf(cell.getNumericCellValue());
                break;
              case BOOLEAN:
                item = String.valueOf(cell.getBooleanCellValue());
                break;
              case FORMULA:
                item = cell.getCellFormula();
                break;
              default:
                item = "";
            }
            resArr[row.getRowNum()][cell.getColumnIndex()] = item;
          }
        }

        Stream.of(resArr).forEach(a -> resList.add(Arrays.asList(a)));
        resMap.put(sheet.getSheetName(), resList);
        // end
      }
    } catch (IOException e) {
      log.error("读取excel异常", e);
    } finally {
      try {
        if (workbook != null) {
          workbook.close();
        }
        if (fileInputStream != null) {
          fileInputStream.close();
        }
      } catch (IOException e) {
        log.error("读取excel异常2", e);
      }
    }

    return resMap;
  }


  public static List<List<String>> readExcel(File file){
    FileInputStream fileInputStream = null;
    Workbook workbook = null;
    List<List<String>> resList = new ArrayList<>();

    try {
      fileInputStream = new FileInputStream(file);
      if (FileTypeUtils.getFileType(file).equals("xls")){
        workbook = new HSSFWorkbook(fileInputStream);
      } else {
        workbook = new XSSFWorkbook(fileInputStream);
      }


      Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表

      Short aShort = Optional.ofNullable(sheet.getRow(0)).map(Row::getLastCellNum)
          .orElseGet(() -> (short) 7);
      String[][] resArr = new String[sheet.getLastRowNum()+1][aShort];
      for (Row row : sheet) {
//        List<String> res = new ArrayList<>();
        String item = "";
        for (Cell cell : row) {
          CellType cellType = cell.getCellType();
          switch (cellType) {
            case STRING:
              item = cell.getStringCellValue();
              break;
            case NUMERIC:
              item = String.valueOf(cell.getNumericCellValue());
              break;
            case BOOLEAN:
              item = String.valueOf(cell.getBooleanCellValue());
              break;
            case FORMULA:
              item = cell.getCellFormula();
              break;
            default:
              item = "";
          }
          resArr[row.getRowNum()][cell.getColumnIndex()] = item;
        }
//        resList[row.getRowNum()][](res);
      }

      Stream.of(resArr).forEach(a -> resList.add(Arrays.asList(a)));
    } catch (IOException e) {
      log.error("读取excel异常", e);
    } finally {
      try {
        if (workbook != null) {
          workbook.close();
        }
        if (fileInputStream != null) {
          fileInputStream.close();
        }
      } catch (IOException e) {
        log.error("读取excel异常2", e);
      }
    }

    return resList;
  }


  public static void downloadFile(String fileURL, String saveDir) throws IOException {
    URL url = new URL(fileURL);
    try (InputStream in = new BufferedInputStream(url.openStream());
        FileOutputStream fileOutputStream = new FileOutputStream(saveDir)) {
      byte[] dataBuffer = new byte[1024];
      int bytesRead;
      while ((bytesRead = in.read(dataBuffer, 0, 1024)) != -1) {
        fileOutputStream.write(dataBuffer, 0, bytesRead);
      }
    } catch (IOException e) {
      log.error("读取远程文件异常", e);
    }
  }

}
